Winzipper/Winzipper_ISO.iso/programming/oracle7 7.2/DB/UTIL72/PRVTPOOL.SQL

Text File | 1995-05-09 | 5.2 KB | 145 lines

rem
rem $Header: prvtpool.sql 7020200.1 95/02/15 18:25:51 cli Generic<base> $
rem
Rem  Copyright (c) 1991 by Oracle Corporation
Rem    NAME
Rem      prvtpool.sql - dbms_shared_pool utility package (private).
Rem    DESCRIPTION
Rem      This package allows you to display the sizes of objects in the
Rem      shared pool, and mark them for keeping or unkeeping in order to
Rem      reduce memory fragmentation.
Rem    RETURNS
Rem
Rem    NOTES
Rem    MODIFIED   (MM/DD/YY)
Rem     bhirano    11/30/94 -  merge changes from branch 1.1.710.2
Rem     rtaranto   10/28/94 -  merge changes from branch 1.1.710.3
Rem     rtaranto   10/18/94 -  doit can now handle keeping triggers
Rem     adowning   03/25/94 -  merge changes from branch 1.1.710.1
Rem     bhirano    09/27/94 -  add aborted_request_threshold icd to dbms_pool
Rem     adowning   02/23/94 -  Branch_for_patch
Rem     adowning   02/23/94 -  Creation
Rem     ajasuja    01/06/94 -  merge changes from branch 1.1.312.1
Rem     rkooi      04/20/93 -  change psdkeep to psdkep
Rem     ajasuja    11/05/93 -  handle UNIX addresses
Rem     rkooi      12/08/92 -  Creation

Rem DBA_KEEPSIZES - size that will be keeped for a pl/sql object
Rem   For instance, do:
Rem     select * from dba_keepsizes where totsize>30 and owner='SCOTT'
Rem     order by totsize desc;
Rem   to see all PL/SQL objects owned by SCOTT which are larger than 30Kbytes
create or replace view dba_keepsizes (totsize, owner, name) as
  select trunc((sum(parsed_size)+sum(code_size))/1000),
         owner, name
  from dba_object_size
  where type in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY','TRIGGER')
  group by owner, name;

create or replace package body dbms_shared_pool is
  procedure psdkep(schema varchar2, name varchar2, cursor_addr raw,
                   cursor_hash binary_integer, keep boolean, flag char);
  pragma interface(C, psdkep);

  procedure psdart(t_size binary_integer);
  pragma interface(C, psdart);

  procedure doit(name varchar2, keep boolean, flag char) is
    owner      varchar2(30);
    part1      varchar2(30);
    part2      varchar2(30);
    dblink     varchar2(30);
    part1_type number;
    objno      number;
    context    number;
  begin
    if flag <> 'P' and flag <> 'p' and flag <> 'R' and flag <> 'r' then
      psdkep('', '', hextoraw(substr(name,1,8)),
         to_number(substr(name,10)), keep, 'c');
    else
      if flag <> 'P' and flag <> 'p' then
    context := 3;
      else
    context := 1;
      end if;
      dbms_utility.name_resolve(name, context, owner, part1, part2, dblink,
                                part1_type, objno);
      psdkep(owner, nvl(part1, part2), null, null, keep, flag);
    end if;
  end;

  procedure keep(name varchar2, flag char) is
  begin
    doit(name, TRUE, flag);
  end;

  procedure unkeep(name varchar2, flag char) is
  begin
    doit(name, FALSE, flag);
  end;

  procedure aborted_request_threshold(threshold_size number) is
    t_size binary_integer;
    t_high binary_integer;
    t_low  binary_integer;
  begin
    t_size := threshold_size;
    t_high := 2147483647;
    t_low  := 5000;
    if t_size >= t_low and t_size <= t_high then
      psdart(t_size);
    else
      raise_application_error(-20000, 'threshold_size not in valid range: (' ||
        t_low || ' - ' || t_high || ')');
    end if;
  end;

  procedure sizes(minsize number) is
    cursor c1(msize number) is
        select to_char(sharable_mem/1000, '999999') sz,
               decode(kept_versions, 0, '      ',
                 rpad('YES(' || to_char(kept_versions) || ')', 6)) keeped,
               rawtohex(address) || ',' || to_char(hash_value) name,
               substr(sql_text,1,354) extra
        from v$sqlarea
        where sharable_mem > msize*1000
      union
        select to_char(sharable_mem/1000, '999999') sz,
               decode(kept, 'YES', 'YES   ', '      ') keeped,
               owner || '.' || name || lpad(' ', 29 - (length(owner) +
                 length(name))) || '(' || type || ')' name,
               null extra
        from v$db_object_cache v
        where sharable_mem > msize*1000
      order by 1 desc;
    l number;
    i number;
  begin
   dbms_output.put_line('SIZE(K) KEPT   NAME');
   dbms_output.put_line('------- ------ ---------------------------------------------------------------');
    for rec in c1(minsize) loop
      if substr(rec.name,1,1) in ('0','1','2','3','4','5','6','7','8','9') then
        l := length(rec.extra);
        i := 0;
        while i < l loop
          if i = 0 then
            dbms_output.put_line(rec.sz || ' ' || rec.keeped || ' ' ||
                                 substr(rec.extra,i,63));
            i := i + 63;
          else
            dbms_output.put_line('                   ' ||
                                 substr(rec.extra,i,59));
            i := i + 59;
          end if;
        end loop;
        dbms_output.put_line(rpad('                   (' || rec.name ||
                             ')', 45) || '(CURSOR)');
      else
        dbms_output.put_line(rec.sz || ' ' || rec.keeped || ' ' || rec.name);
      end if;
    end loop;
  end;
end;
/